/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sarco.bwi.lib.mysql;

import com.sarco.bwi.lib.Node;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author iktsup
 */
public class Mysql {

    public static class Columns {
        public static final String NODE_IDNODE = "idnode";
        public static final String NODE_CURR_FRAME = "curr_frame";
        public static final String NODE_END_FRAME = "end_frame";
        public static final String NODE_PERCENT_DONE = "percent_done";
        public static final String NODE_START_FRAME = "start_frame";
        public static final String NODE_SYSTEM = "system";
        public static final String NODE_TIME_LEFT = "time_left";

        public static final String LOG_IDLOG = "idlog";
        public static final String LOG_IDNODE = "idnode";
        public static final String LOG_DATE = "date";
        public static final String LOG_CURR_FRAME = "curr_frame";
        public static final String LOG_END_FRAME = "end_frame";
        public static final String LOG_PERCENT_DONE = "percent_done";
        public static final String LOG_START_FRAME = "start_frame";

        public static final String JOBS_IDJOBS     = "idjobs";
        public static final String JOBS_IDNODE     = "idnode";
        public static final String JOBS_SFRAME     = "sframe";
        public static final String JOBS_EFRAME     = "eframe";
        public static final String JOBS_IS_TAKEN   = "is_taken";
    }

    public static final class Tables {
        public static final String NODE    = "node";
        public static final String LOG     = "log";
        public static final String JOBS    = "jobs";
    }
    
    private static final String SQL_GET_NODE_DATA = "SELECT * FROM "
            + Tables.NODE;
    private static final String SQL_GET_LOG_DATA = "SELECT * FROM "
            + Tables.LOG;
    private static final String SQL_GET_JOBS_DATA = "SELECT * FROM "
            + Tables.JOBS;

    private static final String SQL_SPLIT_ROW = "##";
    private static final String SQL_SPLIT_COL = "::";
    private Connection conn;
    private Statement stmnt;
    private ResultSet rs;
    private Node[] nodes;

    public static final String DEFAULT_DB   = "bwi";
    public static final String DEFAULT_USER = "root";
    public static final String DEFAULT_PASS = "";
    public static final String DEFAULT_HOST = "127.0.0.1";

    /**
     * The default constructor
     */
    public Mysql() {
        this.conn = null;
        this.stmnt = null;
        this.rs = null;
        this.nodes = null;
    }

    /**
     * Connects to the Mysql database
     * @param host The hostname/ip of the mysql database server
     * (Default: '127.0.0.1')
     * @param user The user to use with the querying (Default: 'root')
     * @param password The password for the mysql user (Default: '')
     * @param database The mysql database ti query (Default: 'bwi')
     * @see com.sarco.bwi.lib.mysql.Misc
     */
    public void connect(String host,
            String user,
            String password,
            String database) {
        String connection = "jdbc:mysql://" + host + "/" + database + "?"
                + "user=" + user + "&password=" + password;
        try {
            this.conn = DriverManager.getConnection(connection);
            this.stmnt = conn.createStatement();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
    /**
     * Fetches the current node data, you need to manually invoke 
     * <code>new Mysql().getNodes();</code><br>
     * Example:<br>
     * <code>
     * Mysql mysql = new Mysql();<br>
     * mysql.fetchNodeData();<br>
     * Node[] nodes = mysql.getNodes();
     * </code><br>
     * It throws a <code>java.sql.SQLException</code>
     * @see com.sarco.bwi.lib.Mysql
     * @see java.sql.Statement
     * @see java.sql.ResultSet
     * @see java.sql.SQLException
     */
    public void fetchNodeData() throws SQLException {
        String[] data = getNodeData(stmnt.executeQuery(SQL_GET_NODE_DATA));
        Node[] sqlnodes = new Node[data.length];
        if (data != null) {
            for (int i = 0; i < data.length; i++) {
                Node n = new Node();
                String[] sData = data[i].split(SQL_SPLIT_COL);
                
                // Id:
                n.setId((sData.length > 0) ? Integer.parseInt(sData[0]) : -1);

                // Current frame:
                n.setCurrFrame((sData.length > 1) ? Integer.parseInt(sData[1]) : -1);

                // End frame:
                n.setEndFrame((sData.length > 2) ? Integer.parseInt(sData[2]) : -1);

                // Percent done:
                n.setiPercentDone((sData.length > 3) ? Integer.parseInt(sData[3]) : -1);

                // Start frame:
                n.setStartFrame((sData.length > 4) ? Integer.parseInt(sData[4]) : -1);

                // System (Operating):
                n.setSystem((sData.length > 5) ? sData[5] : "");

                // Time left:
                n.setTimeLeft((sData.length > 6) ? sData[6] : "");
                
                sqlnodes[i] = n;
            }
            
            this.setNodes(sqlnodes);
        }
    }
    public void closeConnection() throws SQLException {
        if (this.rs != null) {
            this.rs.close();
        }
        if (this.stmnt != null) {
            this.stmnt.close();
        }
        if (this.conn != null) {
            this.conn.close();
        }
        this.rs = null;
        this.stmnt = null;
        this.conn = null;
    }

    private String[] getNodeData(ResultSet rs) throws SQLException {
        String s = "";
        while (rs.next()) {
            int id = rs.getInt(Mysql.Columns.NODE_IDNODE);
            int cf = rs.getInt(Mysql.Columns.NODE_CURR_FRAME);
            int ef = rs.getInt(Mysql.Columns.NODE_END_FRAME);
            int pd = rs.getInt(Mysql.Columns.NODE_PERCENT_DONE);
            int sf = rs.getInt(Mysql.Columns.NODE_START_FRAME);
            String sy = rs.getString(Mysql.Columns.NODE_SYSTEM);
            String tl = rs.getString(Mysql.Columns.NODE_TIME_LEFT); 
            s +=    id + Mysql.SQL_SPLIT_COL + 
                    cf + Mysql.SQL_SPLIT_COL + 
                    ef + Mysql.SQL_SPLIT_COL + 
                    pd + Mysql.SQL_SPLIT_COL + 
                    sf + Mysql.SQL_SPLIT_COL + 
                    sy + Mysql.SQL_SPLIT_COL + 
                    tl + Mysql.SQL_SPLIT_ROW;
        }

        if (s.length() > 0 && s.contains(Mysql.SQL_SPLIT_ROW)) {
            return s.split(Mysql.SQL_SPLIT_ROW);
        } else {
            return null;
        }
    }
    private String[] getJobData(ResultSet rs) {
        String s = "";
        try {
            while (rs.next()) {
                s += rs.getInt(Mysql.Columns.JOBS_IDNODE)
                        + Mysql.SQL_SPLIT_COL
                        + rs.getInt(Mysql.Columns.JOBS_IDNODE)
                        + Mysql.SQL_SPLIT_COL
                        + rs.getInt(Mysql.Columns.JOBS_SFRAME)
                        + Mysql.SQL_SPLIT_COL
                        + rs.getInt(Mysql.Columns.JOBS_EFRAME)
                        + Mysql.SQL_SPLIT_COL
                        + rs.getBoolean(Mysql.Columns.JOBS_IS_TAKEN)
                        + Mysql.SQL_SPLIT_ROW;
            }
        } catch (SQLException ex) {
            Logger.getLogger(Mysql.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        if (s.length() > 0 && s.contains(Mysql.SQL_SPLIT_ROW)) {
            return s.split(Mysql.SQL_SPLIT_ROW);
        } else {
            return null;
        }
    }

    public Node[] getNodes() {
        return nodes;
    }

    public void setNodes(Node[] nodes) {
        this.nodes = nodes;
    }
    
    /**
     * Gets the <code>java.sql.Statement</code>
     * @return the <code>java.sql.Statement</code>
     * @see java.sql.Statement
     */
    public Statement getStatement() {
        return stmnt;
    }
}
